package nguyen.dao;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;

import resource.pojo.Approvedoc;

import models.util.MySqlDataAccessHelper;

public class MyDocumentDAO {

	public static void sendDocument(String databasename, Approvedoc appDoc,
			String[] idemp) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		try {
			String sql = String
					.format("UPDATE approvedoc SET Tile = '%s',Description = '%s', Content = '%s',IDGroupApprove='%s', Timecreate = NOW(),"
							+ " IDStatus=2 WHERE IDApproveDoc = '%s'",
							appDoc.getTitle(), appDoc.getDescription(),
							appDoc.getContent(),appDoc.getiDGroupApprove(), appDoc.getiDApproveDoc());

			mysql.executeUpdate(sql);
			System.out.println("updateappro: " + sql);

			// add listapprove
			String sqlListApp = "insert into listapprove(IDGroupApprove,IDEmployee,IDtypeApprove,`Order`,IDStatus) values('";
			for (int i = 0; i < idemp.length; i++) {
				if (i == 0)
					sqlListApp += appDoc.getiDGroupApprove() + "','"
							+ idemp[i].toString() + "',1," + (i + 1) + "," + 2
							+ "),('";
				else
					sqlListApp += appDoc.getiDGroupApprove() + "','"
							+ idemp[i].toString() + "',1," + (i + 1) + "," + 4
							+ "),('";
			}
			String insertSQL=sqlListApp.substring(0,
					sqlListApp.lastIndexOf(","));
			System.out.println("insertSQL: "+insertSQL);
			int result = mysql.executeUpdate(insertSQL);
			System.out.println("result"+result);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}
	
	public static void sendDocumentForm12(String databasename, Approvedoc appDoc,
			String[] idemp) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		try {
			String sql;
			if(appDoc.isTripDateFlag()){
				String tripDate=(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")).format(appDoc.getTripDate());
				 sql = String
				.format("UPDATE approvedoc SET Tile = '%s',Description = '%s', Content = '%s',IDGroupApprove='%s',EmployeePerforment='%s',Location='%s',TripDate='%s',TotalTime='%s', Timecreate = NOW(),"
						+ " IDStatus=2 WHERE IDApproveDoc = '%s'",
						appDoc.getTitle(), appDoc.getDescription(),
						appDoc.getContent(),appDoc.getiDGroupApprove(),appDoc.getEmployeePerforment(),appDoc.getLocation(),tripDate,appDoc.getTotalTime(), appDoc.getiDApproveDoc());

			}else{
				 sql = String
				.format("UPDATE approvedoc SET Tile = '%s',Description = '%s', Content = '%s',IDGroupApprove='%s',EmployeePerforment='%s',Location='%s',TotalTime='%s', Timecreate = NOW(),"
						+ " IDStatus=2 WHERE IDApproveDoc = '%s'",
						appDoc.getTitle(), appDoc.getDescription(),
						appDoc.getContent(),appDoc.getiDGroupApprove(),appDoc.getEmployeePerforment(),appDoc.getLocation(),appDoc.getTotalTime(), appDoc.getiDApproveDoc());

			}
			
			mysql.executeUpdate(sql);
			System.out.println("updateappro: " + sql);

			// add listapprove
			String sqlListApp = "insert into listapprove(IDGroupApprove,IDEmployee,IDtypeApprove,`Order`,IDStatus) values('";
			for (int i = 0; i < idemp.length; i++) {
				if (i == 0)
					sqlListApp += appDoc.getiDGroupApprove() + "','"
							+ idemp[i].toString() + "',1," + (i + 1) + "," + 2
							+ "),('";
				else
					sqlListApp += appDoc.getiDGroupApprove() + "','"
							+ idemp[i].toString() + "',1," + (i + 1) + "," + 4
							+ "),('";
			}
			String insertSQL=sqlListApp.substring(0,
					sqlListApp.lastIndexOf(","));
			System.out.println("insertSQL: "+insertSQL);
			int result = mysql.executeUpdate(insertSQL);
			System.out.println("result"+result);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}
	
	public static void sendDocumentForm16(String databasename, Approvedoc appDoc,
			String[] idemp) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		try {
			String sql = String
					.format("UPDATE approvedoc SET Tile = '%s',Description = '%s', Content = '%s',IDGroupApprove='%s',Content2='%s', Timecreate = NOW(),"
							+ " IDStatus=2 WHERE IDApproveDoc = '%s'",
							appDoc.getTitle(), appDoc.getDescription(),
							appDoc.getContent(),appDoc.getiDGroupApprove(),appDoc.getContent2(), appDoc.getiDApproveDoc());

			mysql.executeUpdate(sql);
			System.out.println("updateappro: " + sql);

			// add listapprove
			String sqlListApp = "insert into listapprove(IDGroupApprove,IDEmployee,IDtypeApprove,`Order`,IDStatus) values('";
			for (int i = 0; i < idemp.length; i++) {
				if (i == 0)
					sqlListApp += appDoc.getiDGroupApprove() + "','"
							+ idemp[i].toString() + "',1," + (i + 1) + "," + 2
							+ "),('";
				else
					sqlListApp += appDoc.getiDGroupApprove() + "','"
							+ idemp[i].toString() + "',1," + (i + 1) + "," + 4
							+ "),('";
			}
			String insertSQL=sqlListApp.substring(0,
					sqlListApp.lastIndexOf(","));
			System.out.println("insertSQL: "+insertSQL);
			int result = mysql.executeUpdate(insertSQL);
			System.out.println("result"+result);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public static synchronized void deleteDoc(String databasename,
			String idApproveDoc) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		try {
			String sql = "DELETE FROM approvedoc WHERE IDApproveDoc ='"
					+ idApproveDoc + "'";
			System.out.println("sql: " + sql);
			mysql.executeUpdate(sql);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public static Approvedoc getDocument(String databasename,
			String iDApproveDoc) {
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		mysql.open(databasename);
		Approvedoc doc_detail = null;
		try {
			String sql = "select ap.* from approvedoc ap where ap.IDApproveDoc='"
					+ iDApproveDoc + "' limit 1";
			ResultSet rs = mysql.executeQuery(sql);
			while (rs.next()) {
				doc_detail = new Approvedoc();
				doc_detail.setiDApproveDoc(rs.getString("IDApproveDoc"));
				doc_detail.setTitle(rs.getString("Tile"));
				doc_detail.setDescription(rs.getString("Description"));
				doc_detail.setContent(rs.getString("Content"));
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				mysql.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return doc_detail;
	}
}
